DROP TABLE #Device
DROP TABLE #FrequencyAlarms
DROP TABLE #VoltageAlarms
DROP TABLE #Computations
DROP TABLE #PerDeviceComputation
DROP TABLE #ComputationLogic
DROP TABLE #ParsedComputation
DROP TABLE #TMPComputationLogic
DROP TABLE #TmpDevice
DROP TABLE #TMPComputation
DROP TABLE #TmpEmail
DROP TABLE #TmpVar
DROP TABLE #Email

DELETE Alarm;
DELETE Measurement WHERE SignalTypeID IN (SELECT ID FROM SignalType WHERE Acronym LIKE 'ALRM' OR Acronym LIKE 'CALC')
DELETE CustomActionAdapter WHERE TypeName LIKE 'DynamicCalculator.DynamicCalculator'


/* Email Settings */
DECLARE @EmailConn VARCHAR(MAX)
SET @EmailConn = 'SMTPServer=dockerhost.gpa.gridprotectionalliance.org; MultiTriggerPrevention=1; From=email; From=email;  '

/* Computation Settings */
DECLARE @ComputationCon VARCHAR(MAX)
SET @ComputationCon = 'FramesPerSecond=30; LagTime=1; LeadTime=10;'


/* Table of Devices */
SELECT 
	Device.ID AS DeviceID,
	Device.Acronym As Acronym,
	(SELECT TOP 1 PointTag FROM Measurement WHERE SignalTypeID IN (SELECT ID FROM SignalType WHERE Acronym LIKE 'FREQ') AND DeviceID = Device.ID) AS Frequency,
	(SELECT TOP 1 PointTag FROM Measurement WHERE SignalTypeID IN (SELECT ID FROM SignalType WHERE Acronym LIKE 'VPHM') AND DeviceID = Device.ID AND '+' IN (SELECT Phase FROM Phasor WHERE Phasor.DeviceID = Device.ID AND Phasor.SourceIndex = Measurement.PhasorSourceIndex)) AS Voltage,
	(SELECT MAX(BaseKV) FROM Phasor WHERE Phasor.DeviceID = Device.ID) AS BaseKV 
	INTO #Device FROM 
	Device WHERE IsConcentrator <> 1;

SELECT * FROM #Device

/* Table Of Frequency Alarms */
SELECT 
	CAST('Status ALARM Frequency < 45 Hz for ' AS VARCHAR(200)) AS Description,
	CAST('AL-LOW-FREQ-STATUS:' AS VARCHAR(200)) AS Prefix,
	21 AS Operation,
	45.00 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority INTO #FrequencyAlarms

INSERT INTO #FrequencyAlarms SELECT 
	'Status ALARM Frequency > 55 Hz for ' AS Description,
	'AL-HIGH-FREQ-STATUS:' AS Prefix,
	11 AS Operation,
	55.00 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'ALERT ALARM Frequency < 49.80 Hz for ' AS Description,
	'AL-LOW-FREQ-ALERT:' AS Prefix,
	21 AS Operation,
	49.80 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'ALERT ALARM Frequency > 50.20 Hz for ' AS Description,
	'AL-HIGH-FREQ-ALERT:' AS Prefix,
	11 AS Operation,
	50.20 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency > 50.10 Hz over 5 minutes for ' AS Description,
	'AL-HIGH-FREQ-WARN-SHORT:' AS Prefix,
	11 AS Operation,
	50.10 AS SetPoint,
	300 AS Delay,
	50 AS Priority
INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency > 50.05 Hz over 15 minutes for ' AS Description,
	'AL-HIGH-FREQ-WARN-LONG:' AS Prefix,
	11 AS Operation,
	50.05 AS SetPoint,
	900 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency < 49.90 Hz over 5 mintues for ' AS Description,
	'AL-LOW-FREQ-WARN-SHORT:' AS Prefix,
	21 AS Operation,
	49.90 AS SetPoint,
	300 AS Delay,
	50 AS Priority

INSERT INTO #FrequencyAlarms SELECT 
	'WARNING ALARM Frequency < 49.95 Hz over 15 minutes for ' AS Description,
	'AL-LOW-FREQ-WARN-LONG:' AS Prefix,
	21 AS Operation,
	49.95 AS SetPoint,
	900 AS Delay,
	50 AS Priority


/* Table of Voltage Alarms */
SELECT 
	CAST('Status ALARM Voltage < 10KV for ' AS VARCHAR(200)) AS Description,
	CAST('AL-LOW-VOLT-STATUS:' AS VARCHAR(200)) AS Prefix,
	21 AS Operation,
	10000 AS SetPoint,
	1E-5 AS Delay,
	50 AS Priority,
	NULL AS BaseKV
	INTO #VoltageAlarms

INSERT INTO #VoltageAlarms SELECT 
	'WARNING ALARM Voltage < 198 kV for ' AS Description,
	'AL-LOW-VOLT-ALARM:' AS Prefix,
	21 AS Operation,
	198000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	220 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'ALERT ALARM Voltage < 187 kV for ' AS Description,
	'AL-LOW-VOLT-WARN:' AS Prefix,
	21 AS Operation,
	187000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	220 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'WARNING ALARM Voltage > 246 kV for ' AS Description,
	'AL-HIGH-VOLT-ALARM:' AS Prefix,
	11 AS Operation,
	246000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	220 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'ALERT ALARM Voltage > 253 kV for ' AS Description,
	'AL-HIGH-VOLT-WARN:' AS Prefix,
	11 AS Operation,
	253000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	220 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'WARNING ALARM Voltage < 360 kV for ' AS Description,
	'AL-LOW-VOLT-ALARM:' AS Prefix,
	21 AS Operation,
	360000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	400 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'ALERT ALARM Voltage < 340 kV for ' AS Description,
	'AL-LOW-VOLT-WARN:' AS Prefix,
	21 AS Operation,
	340000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	400 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'WARNING ALARM Voltage > 420 kV for ' AS Description,
	'AL-HIGH-VOLT-ALARM:' AS Prefix,
	11 AS Operation,
	420000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	400 AS BaseKV

INSERT INTO #VoltageAlarms SELECT 
	'ALERT ALARM Voltage > 440 kV for ' AS Description,
	'AL-HIGH-VOLT-WARN:' AS Prefix,
	11 AS Operation,
	440000 AS SetPoint,
	30 AS Delay,
	50 AS Priority,
	400 AS BaseKV

/* Computations Per Device */
SELECT 
	CAST('Status Check for ' AS VARCHAR(200)) AS Description,
	CAST('QUALITY-STATUS:' AS VARCHAR(200)) AS Prefix,
	1 AS Count
	INTO #PerDeviceComputation

INSERT INTO #PerDeviceComputation SELECT 
	'Voltage Warning for ' AS Description,
	'VOLT-WARN:' AS Prefix,
	2 AS Count

INSERT INTO #PerDeviceComputation SELECT 
	'Voltage Alert for ' AS Description,
	'VOLT-ALERT:' AS Prefix,
	2 AS Count

INSERT INTO #PerDeviceComputation SELECT 
	'Frequency Warning 5 minute for ' AS Description,
	'FREQ-WARN-SHORT:' AS Prefix,
	3 as Count


INSERT INTO #PerDeviceComputation SELECT 
	'Frequency Warning 15 minute for ' AS Description,
	'FREQ-WARN-LONG:' AS Prefix,
	3 as Count

INSERT INTO #PerDeviceComputation SELECT 
	'Frequency Alert for ' AS Description,
	'FREQ-ALERT:' AS Prefix,
	2 AS COUNT

/* Set up Computation Logic */
SELECT 
	CAST('QUALITY-STATUS:' AS VARCHAR(200)) AS Prefix,
	CAST('Vlow' AS VARCHAR(200)) AS Variable,
	CAST('AL-LOW-VOLT-STATUS:' AS VARCHAR(200)) AS InputPrefix,
	0 AS Invert
	INTO #ComputationLogic
INSERT INTO #ComputationLogic SELECT 
	'QUALITY-STATUS:' AS Prefix,
	'flow' AS Variable,
	'AL-LOW-FREQ-STATUS:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'QUALITY-STATUS:' AS Prefix,
	'fhigh' AS Variable,
	'AL-HIGH-FREQ-STATUS:' AS InputPrefix,
	0 AS Invert

INSERT INTO #ComputationLogic SELECT 
	'VOLT-WARN:' AS Prefix,
	's' AS Variable,
	'QUALITY-STATUS:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'VOLT-WARN:' AS Prefix,
	'Vlow' AS Variable,
	'AL-LOW-VOLT-WARN:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'VOLT-WARN:' AS Prefix,
	'Vhig' AS Variable,
	'AL-HIGH-VOLT-WARN:' AS InputPrefix,
	0 AS Invert

INSERT INTO #ComputationLogic SELECT 
	'VOLT-ALERT:' AS Prefix,
	's' AS Variable,
	'QUALITY-STATUS:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'VOLT-ALERT:' AS Prefix,
	'Vlow' AS Variable,
	'AL-LOW-VOLT-ALARM:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'VOLT-ALERT:' AS Prefix,
	'Vhig' AS Variable,
	'AL-HIGH-VOLT-ALARM:' AS InputPrefix,
	0 AS Invert


INSERT INTO #ComputationLogic SELECT 
	'FREQ-ALERT:' AS Prefix,
	's' AS Variable,
	'QUALITY-STATUS:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-ALERT:' AS Prefix,
	'fhig' AS Variable,
	'AL-LOW-FREQ-ALERT:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-ALERT:' AS Prefix,
	'flow' AS Variable,
	'AL-HIGH-FREQ-ALERT:' AS InputPrefix,
	0 AS Invert


INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-SHORT:' AS Prefix,
	's' AS Variable,
	'QUALITY-STATUS:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-SHORT:' AS Prefix,
	'fhig' AS Variable,
	'AL-HIGH-FREQ-WARN-SHORT:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-SHORT:' AS Prefix,
	'flow' AS Variable,
	'AL-LOW-FREQ-WARN-SHORT:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-SHORT:' AS Prefix,
	'a' AS Variable,
	'FREQ-ALERT:' AS InputPrefix,
	1 AS Invert

INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-LONG:' AS Prefix,
	's' AS Variable,
	'QUALITY-STATUS:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-LONG:' AS Prefix,
	'fhig' AS Variable,
	'AL-HIGH-FREQ-WARN-LONG:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-LONG:' AS Prefix,
	'flow' AS Variable,
	'AL-LOW-FREQ-WARN-LONG:' AS InputPrefix,
	0 AS Invert
INSERT INTO #ComputationLogic SELECT 
	'FREQ-WARN-LONG:' AS Prefix,
	'a' AS Variable,
	'FREQ-ALERT:' AS InputPrefix,
	1 AS Invert

/* Emails */
SELECT 
	CAST('5 minute Frequency Warning' AS VARCHAR(200)) AS Subject,
	CAST('A PMU has measured a Frequency below 49.90Hz or above 50.10hz for more than 5 minutes {LINK}' AS VARCHAR(200)) AS Body,
	CAST('FREQ-WARN-SHORT:' AS VARCHAR(200)) AS Expression
	INTO #Email

INSERT INTO #Email SELECT 
	CAST('15 minute Frequency Warning' AS VARCHAR(200)) AS Subject,
	CAST('A PMU has measured a Frequency below 49.95Hz or above 50.05hz for more than 15 minutes {LINK}' AS VARCHAR(200)) AS Body,
	CAST('FREQ-WARN-LONG:' AS VARCHAR(200)) AS Expression

INSERT INTO #Email SELECT 
	CAST('Frequency Alert' AS VARCHAR(200)) AS Subject,
	CAST('A PMU has measured a Frequency below 49.80Hz or above 50.20Hz {LINK}' AS VARCHAR(200)) AS Body,
	CAST('FFREQ-ALERT:' AS VARCHAR(200)) AS Expression

INSERT INTO #Email SELECT 
	CAST('Voltage Warning' AS VARCHAR(200)) AS Subject,
	CAST('A PMU has measured a Voltage below 0.90pu or above 1.10pu for more than 30 seconds {LINK}' AS VARCHAR(200)) AS Body,
	CAST('VOLT-WARN:' AS VARCHAR(200)) AS Expression

	
INSERT INTO #Email SELECT 
	CAST('Voltage Alert' AS VARCHAR(200)) AS Subject,
	CAST('A PMU has measured a Voltage below 0.85pu or above 1.15pu for more than 30 seconds {LINK}' AS VARCHAR(200)) AS Body,
	CAST('VOLT-ALERT:' AS VARCHAR(200)) AS Expression

/* Create Measurements for Per Device Computation */
INSERT INTO Measurement (DeviceID, HistorianID, SignalTypeID, PointTag, SignalReference, Description, Enabled)
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'STAT'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'CALC'),
	CONCAT(#PerDeviceComputation.Prefix,#Device.Acronym),
	CONCAT(#PerDeviceComputation.Prefix,#Device.Acronym),
	CONCAT(#PerDeviceComputation.Description,#Device.Acronym),
	(SELECT 1)
	FROM #Device CROSS JOIN #PerDeviceComputation 

/* Create Measurements for Alarms */

INSERT INTO Measurement (DeviceID, HistorianID, SignalTypeID, PointTag, SignalReference, Description, Enabled)
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'STAT'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'ALRM'),
	CONCAT(#VoltageAlarms.Prefix,#Device.Acronym),
	CONCAT(#VoltageAlarms.Prefix,#Device.Acronym),
	CONCAT(#VoltageAlarms.Description,#Device.Acronym),
	(SELECT 1)
	FROM #Device CROSS JOIN #VoltageAlarms WHERE #Device.BaseKV = #VoltageAlarms.BaseKV OR #VoltageAlarms.BaseKV IS NULL
UNION
SELECT 
	#Device.DeviceID,
	(SELECT ID FROM Historian WHERE Acronym LIKE 'STAT'),
	(SELECT ID FROM SignalType WHERE Acronym LIKE 'ALRM'),
	CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym),
	CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym),
	CONCAT(#FrequencyAlarms.Description,#Device.Acronym),
	(SELECT 1)
	FROM #Device CROSS JOIN #FrequencyAlarms 

/* Create Alarms */

INSERT INTO Alarm (NodeID, TagName, Tolerance, SignalID, Delay, Description, Hysteresis, AssociatedMeasurementID, SetPoint, Severity, Operation) 
SELECT 
	(SELECT TOP 1 ID FROM Node), 
	CONCAT(#VoltageAlarms.Prefix,#Device.Acronym),
	NULL,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE #Device.Voltage),
	#VoltageAlarms.Delay, 
	CONCAT(#VoltageAlarms.Description, #Device.Acronym),
	1E-5,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE CONCAT(#VoltageAlarms.Prefix,#Device.Acronym)),
	#VoltageAlarms.SetPoint,
	#VoltageAlarms.Priority,
	#VoltageAlarms.Operation
	FROM #Device CROSS JOIN #VoltageAlarms WHERE #Device.BaseKV = #VoltageAlarms.BaseKV OR #VoltageAlarms.BaseKV IS NULL
UNION
SELECT 
	(SELECT TOP 1 ID FROM Node), 
	CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym),
	NULL,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE #Device.Frequency),
	#FrequencyAlarms.Delay, 
	CONCAT(#FrequencyAlarms.Description, #Device.Acronym),
	1E-5,
	(SELECT SignalID FROM Measurement WHERE PointTag LIKE CONCAT(#FrequencyAlarms.Prefix,#Device.Acronym)),
	#FrequencyAlarms.SetPoint,
	#FrequencyAlarms.Priority,
	#FrequencyAlarms.Operation
	FROM #Device CROSS JOIN #FrequencyAlarms 


/* Generate Computations */
SELECT #Device.*
INTO  #TmpDevice FROM #Device 

declare @name Varchar(max)
declare @alarm Varchar(200)

SELECT 
	CAST('' AS VARCHAR(MAX)) AS [Name],
	CAST('' AS VARCHAR(MAX)) AS VariableList,
	CAST('' AS VARCHAR(MAX)) AS Expression,
	CAST('' AS VARCHAR(MAX)) AS OutputMeasurement
INTO #ParsedComputation
FROM  #Device WHERE 1 = 0


while exists (select 1 from #TmpDevice)
begin
     select top 1 @name = Acronym from #TmpDevice 

	 SELECT #PerDeviceComputation.* INTO #TMPComputation FROM #PerDeviceComputation

	 while exists (select 1 from #TMPComputation)
	 begin
		select top 1 @alarm = Prefix from #TMPComputation

		SELECT #ComputationLogic.*,
			(SELECT CASE INVERT WHEN 0 THEN Variable ELSE CONCAT('(1-',Variable,')') END ) AS InvertedVar,
			CONCAT(Variable, '=',
			(SELECT SignalID FROM Measurement WHERE PointTag LIKE CONCAT(InputPrefix,@name))) AS VarDefinition
			INTO #TMPComputationLogic FROM #ComputationLogic WHERE #ComputationLogic.Prefix = @alarm


		
		INSERT INTO #ParsedComputation SELECT
		CONCAT(@alarm, @name) AS [Name],
		(SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT(@alarm,@name)) AS OutputMeasurement,
		(SELECT STRING_AGG(VarDefinition,';') FROM #TMPComputationLogic) AS VariableList,
		CONCAT('If((', 
		(SELECT STRING_AGG(InvertedVar,'+') FROM #TMPComputationLogic),
		') >= ', (SELECT COUNT FROM #TMPComputation WHERE Prefix = @alarm),', 1, 0)') AS Expression

		Drop Table #TMPComputationLogic
		delete from #TMPComputation where Prefix = @alarm

	 end     
     delete from #TMPDevice where Acronym = @name
	 DROP Table #TMPComputation
	 
end


INSERT INTO CustomActionAdapter (AssemblyName, TypeName, AdapterName, NodeID,Enabled,ConnectionString)
SELECT
'DynamicCalculator.dll',
'DynamicCalculator.DynamicCalculator',
Name,
(SELECT TOP 1 ID FROM Node),
(SELECT 1),
CONCAT(
	@ComputationCon,
	' OutputMeasurements=',
	OutputMeasurement,
	'; VariableList={',
	VariableList,
	'}; ExpressionText=', Expression
	)
FROM #ParsedComputation

-- Enable All Alarms
UPDATE ALARM SET Enabled = 1

--Generate Emails

declare @Prefix Varchar(max)

SELECT #Email.* INTO #TmpEmail FROM #Email

while exists (select 1 from #TmpEmail)
begin
     select top 1 @Prefix = #TmpEmail.Expression from #TmpEmail 

	 SELECT
		CONCAT('d', ROW_NUMBER() OVER (ORDER BY Acronym), '=', (SELECT TOP 1 SignalID FROM ActiveMeasurement WHERE PointTAG LIKE CONCAT(@Prefix,#Device.Acronym)) ) AS Variable,
		CONCAT('d', ROW_NUMBER() OVER  (ORDER BY Acronym)) AS Expresion 
	 INTO #TmpVar FROM #Device

	 INSERT INTO CustomActionAdapter (AssemblyName, TypeName, AdapterName, NodeID,Enabled,ConnectionString) SELECT
			'DynamicCalculator.dll',
			'DynamicCalculator.EmailNotifier',
			@Prefix,
			(SELECT TOP 1 ID FROM Node),
			(SELECT 1),
			CONCAT(
			@EmailConn,
			'; Body=''',
			(SELECT TOP 1 Body FROM #Email WHERE Expression = @Prefix),
			'''; Subject=''',
			(SELECT TOP 1 Subject FROM #Email WHERE Expression = @Prefix),
			'''',
			'; VariableList={',
			(SELECT STRING_AGG(Variable,';') FROM #TmpVar),
			'}; ExpressionText=', 
			(SELECT STRING_AGG(Expresion,' + ') FROM #TmpVar) 
			, ' > 0;')

	
	 DROP TABLE #TmpVar
	 
     delete from #TmpEmail where Expression = @Prefix 
end